"""数据存储文件"""

import logging


import time
from database.connection import Link_database
# from ydsp.database.connection import Link_database

logger = logging.getLogger(__name__)


def SubmitExpenseSQL(statdate,bx_department, bx_name, bx_category, bx_money, file_address, note):
    """报销存储"""
    con, obj = Link_database()

    sql = """insert into submit_expense (statdate,bx_department,bx_name,bx_category,bx_money,file_address,note) values (%d,%d,'%s','%s','%s','%s','%s')""" % (statdate,bx_department, bx_name, bx_category, bx_money, file_address, note)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def DelSubmitExpenseSQL(id):
    """报销删除"""
    con, obj = Link_database()

    sql = """delete from submit_expense where id=%d""" % id
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def ExpenseSQL(statdate, shop_name, refund_time, refund_money, note):
    """退款存储"""
    con, obj = Link_database()

    sql = """insert into expense (statdate, shop_name, refund_time, refund_money, note) values (%d,'%s','%s','%s','%s')""" % (statdate, shop_name, refund_time, refund_money, note)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def DelExpenseSQL(id):
    """报销删除"""
    con, obj = Link_database()

    sql = """delete from expense where id=%d""" % id
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def FinancialPackageSQL(statdate,file_name, file_address):
    """财务报表存储"""
    con, obj = Link_database()

    sql_1 = """select id from financial where file_name='%s'""" % file_name
    obj.execute(sql_1)
    if not obj.fetchone():
        sql = """insert into financial (statdate,file_name,file_address) values ('%s','%s','%s')""" % (statdate, file_name, file_address)
        obj.execute(sql)
        con.commit()

        obj.close()
        con.close()
        return True
    else:
        return False


class WageSQL(object):
    """工资表存储"""
    def __init__(self, *args):
        # user_id, basis_salary, subsidies, service_fee, sales_some, performance, social, statdate, company_social
        self.user_id = args[0]
        self.basis_salary = args[1]  # 基础薪资
        # self.birthday_allowance = args[2]  # 生日津贴
        self.subsidies = args[2]  # 饭补
        # self.late = args[4]  # 迟到
        # self.business_allowance = args[3]  # 出差补贴
        self.service_fee = args[3]  # 服务提成
        self.sales_some = args[4]  # 销售额提点
        self.performance = args[5]  # 绩效
        self.social = args[6]  # 个人社保部分
        self.company_social = args[8]  # 公司交社保部分
        self.statdate = args[7]  # 时间字符串
        # self.be_attendance = args[11]  # 应出勤天数
        # self.full_time = args[12]  # 全勤奖
        self.con, self.obj = Link_database()

    def sel_attendance(self):
        """查询考勤"""
        sql = """select be_attendance_days,attendance_days,work_overtime,leave_record,late_time,business_travel_day from attendance where user_id={} and statistics_time='{}'""".format(self.user_id, self.statdate)
        self.obj.execute(sql)
        q = self.obj.fetchone()

        return q

    def sel_birthday_allowance(self):
        """查看生日"""
        sql = """select born_date from user where id={}""".format(self.user_id)
        self.obj.execute(sql)
        a = self.obj.fetchone()[0]  # 生日时间
        m = '0'
        if a[5:-3] == self.statdate[5:]:
            m = '100'

        return m

    def deal_late(self, late_time, b):
        """迟到费用"""
        late_time = float(late_time)
        late = '0'
        if 30 < late_time <= 60:
            late = str((late_time - 30) * 2)
        elif late_time > 60:
            late = str(round(b / 2, 2))

        return late

    def sel_full(self, be_attendance, real_attendance, late_time):
        """全勤奖"""
        sql = """select state from user where id={}""".format(self.user_id)
        self.obj.execute(sql)
        q = self.obj.fetchone()
        full_time = '0'  # 全勤奖
        if float(be_attendance) - float(real_attendance) < 1.1 and float(late_time) <= 60 and q[0] != '实习生':
            full_time = '200'

        salary = str(int(self.basis_salary) + 200)  # 薪资
        if q[0] == '实习生':
            salary = self.basis_salary

        return full_time, salary

    def deal_with(self):
        """整理数据"""
        statdate = int(time.mktime(time.strptime(self.statdate, '%Y-%m')))  # 时间

        try:
            be_attendance, real_attendance, work_overtime, ask_leave, late_time, business_travel_day = self.sel_attendance()  # 应出勤天数，实出勤天数，加班天数，请假天数，迟到时长, 出差天数
        except Exception as e:
            return False

        full_time, salary = self.sel_full(be_attendance, real_attendance, late_time)  # 全勤奖, 薪资
        birthday_allowance = self.sel_birthday_allowance()  # 生日津贴
        b = int(self.basis_salary) / int(be_attendance)  # 每天工资
        leave_cost = str(round(b * float(ask_leave), 2)) if ask_leave else '0'  # 请假费用
        work_overtime_cost = str(round(b * float(work_overtime), 2)) if float(work_overtime) > 0 else '0'  # 加班费
        late = self.deal_late(late_time, b)  # 迟到费
        business_allowance = float(business_travel_day) * 50  # 出差补贴

        real_wages = str(round(b * float(real_attendance) + float(birthday_allowance) + float(self.subsidies) + float(work_overtime_cost) + float(business_allowance) + float(self.service_fee) + float(self.sales_some) + float(self.performance) + float(full_time) - float(late) - float(self.social)))

        sql_1 = """select id from wage where statdate=%d and user_id=%d""" % (statdate, self.user_id)
        self.obj.execute(sql_1)
        if self.obj.fetchone():
            return 2

        sql = """insert into wage values (0,%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d)""" \
              % (statdate, salary, self.basis_salary, full_time, be_attendance, real_attendance, birthday_allowance,
                 self.subsidies, late, ask_leave, leave_cost, work_overtime, work_overtime_cost, business_allowance,
                 self.service_fee, self.sales_some, self.performance, self.social, self.company_social, real_wages, self.user_id)
        self.obj.execute(sql)
        self.con.commit()

        return True

    def __del__(self):
        self.obj.close()
        self.con.close()


def del_WageSQL(id):
    """工资删除"""
    con, obj = Link_database()

    sql = """delete from wage where id={}""".format(id)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def ToDeductedSQL(statdate, company, project, money, take_back, collection_date, collection_account):
    """待扣款存储"""
    con, obj = Link_database()

    sql = """insert into to_deducted (statdate,company,project,money,take_back,collection_date,collection_account) values (%d,'%s','%s','%s','%s','%s','%s')""" % (statdate, company, project, money, take_back, collection_date, collection_account)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def SubmitTypeSQL(species):
    """报销类别存储"""
    con, obj = Link_database()

    sql = """insert into submit_type (species) values ('{}')""".format(species)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def IncomeTypeSQL(category):
    """收入类别存储"""
    con, obj = Link_database()

    sql = """insert into income_type (category) values ('{}')""".format(category)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def SumIncomeSQL(category, *args):
    """总收入存储"""
    con, obj = Link_database()

    sql = """insert into sum_income  values (0,{},'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')"""\
        .format(args[15], category, args[0], args[1], args[2], args[3], args[4], args[5], args[6], args[7], args[8], args[9], args[10], args[11], args[12], args[13], args[14])
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()


def DelSumIncomeSQL(id):
    """收入删除"""
    con, obj = Link_database()

    sql = """delete from sum_income where id={}""".format(id)
    obj.execute(sql)
    con.commit()

    obj.close()
    con.close()